﻿Imports System.IO
Imports System.Text
Imports System.Windows
Imports System.Windows.Controls
Imports ComponentPro.Excel
Imports ComponentPro.Excel.Charts

Namespace LightSwitchApplication

    Public Class SearchAnimals

        Private Sub CreateWorksheet_Execute()

        End Sub

        Private Sub CreateChartData(ByVal sheet As Worksheet)

            'Put values for rows in column A
            sheet.Range("B3").Text = "North America"
            sheet.Range("C3").Text = "Europe"

            sheet.Range("A4").Text = "Jan"
            sheet.Range("A5").Text = "Feb"
            sheet.Range("A6").Text = "March"
            sheet.Range("A7").Text = "Apr"
            sheet.Range("A8").Text = "May"
            sheet.Range("A9").Text = "June"
            sheet.Range("A10").Text = "July"
            sheet.Range("A11").Text = "Aug"
            sheet.Range("A12").Text = "Sept"
            sheet.Range("A13").Text = "Oct"
            sheet.Range("A14").Text = "Nov"
            sheet.Range("A15").Text = "Dec"

            sheet.Range("B4").Number = 10.9
            sheet.Range("B5").Number = 8.9
            sheet.Range("B6").Number = 8.6
            sheet.Range("B7").Number = 4.8
            sheet.Range("B8").Number = 3.2
            sheet.Range("B9").Number = 1.4
            sheet.Range("B10").Number = 0.6
            sheet.Range("B11").Number = 0.7
            sheet.Range("B12").Number = 1.7
            sheet.Range("B13").Number = 5.4
            sheet.Range("B14").Number = 9.0
            sheet.Range("B15").Number = 10.4

            sheet.Range("C4").Number = 47.5
            sheet.Range("C5").Number = 48.7
            sheet.Range("C6").Number = 48.9
            sheet.Range("C7").Number = 50.2
            sheet.Range("C8").Number = 53.1
            sheet.Range("C9").Number = 56.3
            sheet.Range("C10").Number = 58.1
            sheet.Range("C11").Number = 59.0
            sheet.Range("C12").Number = 58.5
            sheet.Range("C13").Number = 55.4
            sheet.Range("C14").Number = 51.1
            sheet.Range("C15").Number = 47.8
            sheet.UsedRange.AutoFitColumns()
        End Sub

        Private Sub SearchAnimals_Created()
            Dim xWorksheet = Me.FindControl("CreateWorksheet")
            AddHandler xWorksheet.ControlAvailable, AddressOf xWorksheet_ControlAvailable
        End Sub

        Private Sub xWorksheet_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)
            RemoveHandler Me.FindControl("CreateWorksheet").ControlAvailable, AddressOf xWorksheet_ControlAvailable
            Dim Button = DirectCast(e.Control, Button)
            AddHandler Button.Click, AddressOf xWorksheet_Click

        End Sub

        Private Sub xWorksheet_Click(sender As Object, e As System.Windows.RoutedEventArgs)
            ' Write your code here.
            Dim dlg As New SaveFileDialog()

            dlg.Filter = "Excel files (.xlsx;.xls)|*.xlsx;*.xls|All Files (*.*)|*.*"
            dlg.FilterIndex = 1

            If dlg.ShowDialog() = True Then
                Dim message As String = Nothing

                Try
                    ' Create a new instance of the WorkbookManager class.
                    Dim manager As New WorkbookManager()

                    ' Add a workbook.
                    Dim workbook As Workbook = manager.Workbooks.Add()

                    If System.IO.Path.GetExtension(dlg.SafeFileName) = ".xls" Then
                        workbook.Version = ComponentPro.Excel.ExcelVersion.Excel97to2003
                    Else
                        workbook.Version = ComponentPro.Excel.ExcelVersion.Excel2007
                    End If
                    Dim sheet As Worksheet = workbook.Worksheets(0)

                    CreateChartData(sheet)

                    ' Hide gridlines.
                    sheet.IsGridLinesVisible = False

                    '					#Region "Chart"
                    ' Create Chart

                    Dim chart As Chart = workbook.Charts.Add()
                    chart.Name = "Cylinder Chart"
                    chart.ChartTitle = "Cylinder Chart - Sales by Region"

                    chart.DataRange = sheet.Range("A3:C15")

                    chart.ChartType = ChartType.Cylinder_Clustered_3D
                    chart.IsSeriesInRows = False
                    chart.Legend.Position = LegendPositionType.Bottom
                    chart.Legend.IsVerticalLegend = False

                    chart.Activate()

                    '					#End Region

                    Using fileStream As Stream = dlg.OpenFile()
                        workbook.SaveAs(fileStream)
                        ' Close the document.
                        workbook.Close()
                    End Using

                    message = "File saved successfully."

                Catch ex As Exception
                    message = "Error saving Excel file. Error: " & ex.Message
                End Try

                'lblMessage.Content = message
            End If
        End Sub

    End Class

End Namespace
